<?php
session_start(); // 开启session功能

/*
 * MyAdminLite
 * 开源作品-PHP在线MySQL管理工具(单文件绿色版)-MyAdminLite 1.0
 * 程序特性：
 *      1. 单文件绿色版,部署超简单
 *      2. 支持显示所有数据库、表、存储过程、函数、视图等，支持查看数据表、视图、存储程序等对象结构
 *      3. 支持查看数据库引擎变量信息
 *      4. 支持用户登录验证、IP验证等多种权限验证功能
 *      5. 支持多个数据库服务器切换
 * 使用方法：
 *      1. 复制本文件到web任意目录
 *      2. 修改$authconfig和$databasecfgs等配置信息为实际内容
 *      3. 在浏览器输入链接路径,开始使用
 * 说明：写这个程序主要是为了辅助项目开发中的数据库记录查看和数据库管理.这个小程序提供了程序特性描述的功能(足以应付项目开发需要). 如果您有兴趣一起完善这个小程序,欢迎使用git一起开发~感谢！
 * 项目周期：
 *      2017-8-23 立项开发
 *      2017-8-24 程序完成
 * 版权：不限商业用途! 如有修改代码请保留原著署名,并把修改后的代码回馈到作者邮箱：<14507247@qq.com>,促进本产品进步.做一个有公德心的正能量程序员~
 *      作者博客：http://www.cnblogs.com/sochishun/ 欢迎交流~
 * @since 1.0 <2017-8-23> SoChishun <14507247@qq.com> Added.
 */

/**
 * 页面访问验证配置
 */
$authconfig = [
    'admins' => ['admin' => 'admin123', 'testuser' => 'testpassword'], // 允许登录的帐号列表['登录名'=>'密码']
    'ips' => ['127.0.0.1', '120.42.15.100'], // 允许访问的ip地址列表
    'current_authtype' => 3, // 指定页面访问验证类型：0:无需验证;1:帐号密码验证;2:IP验证;3:混合验证(先IP验证,IP验证失败则转入帐号密码验证
];
// 数据库配置
$databasecfgs = [
    /* 默认数据库服务器 */
    'default' => [
        'type' => 'mysql',
        'host' => 'rm-test.mysql.rds.aliyuncs.com',
        'user' => 'testuser',
        'password' => 'testpassword',
        'port' => 3306,
        'database' => 'testdb',
        'charset' => 'utf8',
    ],
    /* 本地数据库服务器 */
    'local' => [
        'type' => 'mysql',
        'host' => '127.0.0.1',
        'user' => 'root',
        'password' => 'root',
        'port' => 3306,
        'database' => 'testdb',
        'charset' => 'utf8',
    ],
    'current_database' => 'local', // 指定当前活动的数据库服务器
];
// 版本号
define('VERSION', '1.0.0');
// 缓存前缀
define('SESSION_PREFIX', 'madmlite');

// 访问权限验证
$current_authtype = $authconfig['current_authtype'];
$authstatus = true; // 权限验证状态
if ($current_authtype == 2 || $current_authtype == 3) { // IP验证
    $ip = $_SERVER['REMOTE_ADDR'];
    if (!in_array($ip, $authconfig['ips'])) {
        $authstatus = false; // IP验证失败
        if ($current_authtype == 2) {
            exit('IP [' . $ip . '] 禁止访问!');
        }
    }
}
if ($current_authtype == 1 || ($current_authtype==3 && !$authstatus)) { // 帐号密码验证
    $loginid = isset($_SESSION[SESSION_PREFIX . 'user']) ? $_SESSION[SESSION_PREFIX . 'user'] : false;
    $token = get_token();
    if (!$token) {
        $token = generate_token();
    }
// 注销登录
    if (input('action') == 'logout') {
        logout();
        exit;
    }
    if (!$loginid) {
        checklogin($token, $authconfig['admins']);
        exit;
    }
}

// 浏览器参数处理
$dbcfg = $databasecfgs[$databasecfgs['current_database']];
$curdbname = input('db', empty($dbcfg['database']) ? '' : $dbcfg['database']); // 当前数据库名称
$curtable = trim(input('table')); // 当前表名称
$curview = trim(input('view')); // 当前视图名称
$curroutine = trim(input('routine')); // 当前存储程序名称
$curroutinetype = trim(input('routinetype')); // 当前存储程序类型
$action = trim(input('action')); // 当前动作
$sqlstmt = trim(input('sqlstmt')); // 当前sql语句
if ($curdbname) {
    $dbcfg['database'] = $curdbname; // 指定数据库
}
// 实例化数据库操作类
try {
    $dbutil = new DbUtil($dbcfg);
} catch (Exception $ex) {
    echo $ex->getMessage();
    exit;
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>MyAdminLite - <?php echo VERSION ?></title>
        <style type="text/css">
            body, td, th{font-size:13px; color:#333;}
            aside {width:180px; float:left;}
            aside fieldset div { max-height: 600px; overflow: auto;}
            aside li a:last-child {display:inline-block; width:100px;overflow: hidden;white-space: nowrap; text-overflow: ellipsis;}
            a{text-decoration: none;}
            ul{margin:0;}
            li{list-style: none; margin-left: -40px;}
            li.active { font-weight: bold; color:#F00;}
            li.active a{ color:#F00;}
            .grid { border:solid 1px #CCC; border-bottom:none; border-right: none;}
            .grid tr:hover{background-color: #efefef;}
            .grid th, .grid td { border:solid 1px #CCC; border-left:none; border-top:none;}
            .propertyinfo td { width:360px; word-break: break-all;}
            main { max-width: 960px; float:left;}
            .resultgrid { max-width:960px;max-height: 600px; overflow: auto; margin-top:5px;}
            .resultgrid td { font-size:12px;}
            .resultgrid tr:hover{background-color: #efefef;}
        </style>
    </head>
    <body>
        <?php
        // 用户登录信息
        if (isset($loginid)) {
            echo $loginid;
            echo ' [<a href="?action=logout" onclick="return confirm(\'您确定要注销登录吗?\');">注销登录</a>]';
        }
        ?>
        <fieldset style="max-width:1115px;">
            <legend>数据库信息 <a href="#" title="展开">[+]</a></legend>
            <?php
            $dbvariables = $dbutil->getDbVariables();
            echo '<div style="max-height:300px; overflow:auto;display:none;">';
            echo '<table cellpadding="0" cellspacing="0" class="grid propertyinfo"><tr>';
            $i = 0;
            foreach ($dbvariables as $row) {
                if ($i > 0 && ($i % 3 == 0)) {
                    echo '</tr><tr>';
                }
                echo '<th>', $row['VARIABLE_NAME'], '</th><td>', $row['VARIABLE_VALUE'], '</td>';
                $i++;
            }
            echo '</tr></table>';
            echo '</div>';
            ?>
        </fieldset>
        <aside>
            <?php
            // 侧边栏显示对象名称列表
            $list = $dbutil->getDatabases(); // SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
            echo '选择数据库：<select><option value="">==选择数据库==</option>';
            foreach ($list as $row) {
                $objname = $row['SCHEMA_NAME'];
                echo '<option value="', $objname, '"', ($curdbname == $objname ? ' selected="selected"' : ''), '>', short_text($objname, 12), ' [', $row['DEFAULT_CHARACTER_SET_NAME'], ']</option>';
            }
            echo '</select>';
            if ($curdbname) {
                echo '[<a href="?db=', $curdbname, '&action=variables" title="查看数据库变量信息">I</a>]';
                echo '[<a href="?db=', $curdbname, '&action=create" title="查看创建数据库语句">C</a>]';
            }
            // 数据表列表
            echo '<fieldset><legend>数据表：</legend>';
            echo '<div>';
            if ($curdbname) {
                $list = $dbutil->getTables($curdbname); // TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT
                $i = 1;
                $url = '';
                echo '<ul>';
                foreach ($list as $row) {
                    $objname = $row['TABLE_NAME'];
                    $url = '?db=' . $curdbname . '&table=' . $objname . '&action=egselect';
                    echo '<li', ($curtable == $objname ? ' class="active"' : ''), '>', $i, '. ';
                    echo '<a href="', $url, '" title="', $objname, '">', $objname, '</a></li>';
                    $i++;
                }
                echo '</ul>';
            }
            echo '</div></fieldset>';
            // 视图列表
            echo '<fieldset><legend>视图：</legend>';
            echo '<div>';
            if ($curdbname) {
                $list = $dbutil->getViews($curdbname); // TABLE_NAME
                $i = 1;
                echo '<ul>';
                foreach ($list as $row) {
                    $objname = $row['TABLE_NAME'];
                    echo '<li', ($curtable == $objname ? ' class="active"' : ''), '>', $i, '. <a href="?db=', $curdbname, '&view=', $objname, '&action=egselect" title="', $objname, '">', $objname, '</a></li>';
                    $i++;
                }
                echo '</ul>';
            }
            echo '</div></fieldset>';
            // 程序列表
            echo '<fieldset><legend>程序：</legend>';
            echo '<div>';
            if ($curdbname) {
                $list = $dbutil->getRoutine($curdbname); // ROUTINE_NAME, ROUTINE_TYPE
                $i = 1;
                echo '<ul>';
                foreach ($list as $row) {
                    $objname = $row['ROUTINE_NAME'];
                    echo '<li', ($curroutine == $objname ? ' class="active"' : ''), '><a href="?db=', $curdbname, '&routine=', $objname, '&routinetype=', strtolower($row['ROUTINE_TYPE']), '" title="', $objname, '">', $i, '. [', strtolower(substr($row['ROUTINE_TYPE'], 0, 3)), ']', $objname, '</a></li>';
                    $i++;
                }
                echo '</ul>';
            }
            echo '</div></fieldset>';
            ?>
        </aside>
        <main>
            <?php
            // 主内容显示具体操作和查询编辑器、执行结果窗口
            if ($curdbname) {
                if ($curtable) { // 数据表操作
                    echo '<div>数据表操作示例语句: ';
                    $url = '?db=' . $curdbname . '&table=' . $curtable;
                    echo '<a href="', $url, '&action=egselect" title="查询记录语句示例">SELECT</a> | ';
                    echo '<a href="', $url, '&action=egupdate" title="更新记录语句示例">UPDATE</a> | ';
                    echo '<a href="', $url, '&action=eginsert" title="插入记录语句示例">INSERT</a> | ';
                    echo '<a href="', $url, '&action=egdelete" title="删除记录语句示例">DELETE</a> | ';
                    echo '<a href="', $url, '&action=egstructure" title="查看表结构语句示例">STRUCTURE</a> | ';
                    echo '<a href="', $url, '&action=egalter" title="更改表结构语句示例">ALTER</a> | ';
                    echo '<a href="', $url, '&action=egcreate" title="创建表结构语句示例">CREATE</a>';
                    echo '</div>';
                    $list = $dbutil->getColumns($curdbname, $curtable); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
                    $i = 0;
                    $fields = []; // 字段名称集合
                    echo '<div><textarea cols="134" rows="3">数据表字段：';
                    foreach ($list as $row) {
                        $objname = '`' . $row['COLUMN_NAME'] . '`';
                        if ($i > 0) {
                            echo ', ';
                        }
                        $fields[] = $objname;
                        echo $objname;
                        $i = 1;
                    }
                    echo '</textarea></div>';
                    // 获取示例语句
                    switch ($action) {
                        case 'egselect':
                            $sqlstmt = 'select ' . implode(', ', $fields) . ' from ' . $curtable . ' order by ' . $fields[0] . ' desc limit 50;';
                            break;
                        case 'egupdate':
                            $sqlstmt = 'eg. update ' . $curtable . ' set ' . implode('=?, ', $fields) . '=? where ' . $fields[0] . '=?;';
                            break;
                        case 'eginsert':
                            $sqlstmt = 'eg. insert into ' . $curtable . ' (' . implode(', ', $fields) . ') values (:' . implode(', :', $fields) . ');';
                            break;
                        case 'egdelete':
                            $sqlstmt = 'eg. delete from ' . $curtable . ' where ' . $fields[0] . '=?;';
                            break;
                        case 'egstructure':
                            $sqlstmt = "select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='$curdbname' AND TABLE_NAME='$curtable';";
                            break;
                        case 'egalter':
                            $sqlstmt = "eg. alter table $curtable add column xxx int not null default 0 comment '';";
                            break;
                        case 'egcreate':
                            $sqlstmt = 'show create table ' . $curtable;
                            break;
                    }
                }
                if ($curview) { // 视图操作
                    echo '<div>视图操作示例语句: ';
                    $url = '?db=' . $curdbname . '&view=' . $curview;
                    echo '<a href="', $url, '&action=egselect" title="查询记录语句示例">SELECT</a> | ';
                    echo '<a href="', $url, '&action=egupdate" title="更新记录语句示例">UPDATE</a> | ';
                    echo '<a href="', $url, '&action=eginsert" title="插入记录语句示例">INSERT</a> | ';
                    echo '<a href="', $url, '&action=egdelete" title="删除记录语句示例">DELETE</a> | ';
                    echo '<a href="', $url, '&action=egstructure" title="查看视图结构语句示例">STRUCTURE</a> | ';
                    echo '<a href="', $url, '&action=egalter" title="更改视图结构语句示例">ALTER</a> | ';
                    echo '<a href="', $url, '&action=egcreate" title="创建视图结构语句示例">CREATE</a>|';
                    echo '</div>';
                    $list = $dbutil->getColumns($curdbname, $curview); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
                    $i = 0;
                    $fields = []; // 字段名称集合
                    echo '<div><textarea cols="134" rows="3">视图字段：';
                    foreach ($list as $row) {
                        $objname = '`' . $row['COLUMN_NAME'] . '`';
                        if ($i > 0) {
                            echo ', ';
                        }
                        $fields[] = $objname;
                        echo $objname;
                        $i = 1;
                    }
                    echo '</textarea></div>';
                    // 获取示例语句
                    switch ($action) {
                        case 'egselect':
                            $sqlstmt = 'select ' . implode(', ', $fields) . ' from ' . $curview . ' order by ' . $fields[0] . ' desc limit 50;';
                            break;
                        case 'egupdate':
                            $sqlstmt = 'eg. update ' . $curview . ' set ' . implode('=?, ', $fields) . '=? where ' . $fields[0] . '=?;';
                            break;
                        case 'eginsert':
                            $sqlstmt = 'eg. insert into ' . $curview . ' (' . implode(', ', $fields) . ') values (:' . implode(', :', $fields) . ');';
                            break;
                        case 'egdelete':
                            $sqlstmt = 'eg. delete from ' . $curview . ' where ' . $fields[0] . '=?;';
                            break;
                        case 'egstructure':
                            $sqlstmt = "select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='$curdbname' AND TABLE_NAME='$curview';";
                            break;
                        case 'egalter':
                            $sqlstmt = "eg. create or replace view $curview as ...;";
                            break;
                        case 'egcreate':
                            $sqlstmt = 'show create view ' . $curview . ';';
                            break;
                    }
                }
                if ($curroutine) { // 存储程序操作 // 视图操作
                    echo '<div>程序操作示例语句: ';
                    $url = '?db=' . $curdbname . '&routine=' . $curroutine . '&routinetype=' . $curroutinetype;
                    echo '<a href="', $url, '&action=egcall" title="执行程序语句示例">CALL</a> | ';
                    echo '<a href="', $url, '&action=egdrop" title="删除程序语句示例">DROP</a> | ';
                    echo '<a href="', $url, '&action=egcreate" title="创建程序结构语句示例">CREATE</a>|';
                    echo '</div>';
                    // 获取示例语句
                    switch ($action) {
                        case 'egcall':
                            $sqlstmt = 'eg. ' . ($curroutinetype == 'function' ? 'select ' : 'call ') . $curroutine . '();';
                            break;
                        case 'egdrop':
                            $sqlstmt = 'eg. drop ' . $curroutinetype . ' if exists ' . $curroutine . ';';
                            break;
                        case 'egcreate':
                            $sqlstmt = 'show create ' . $curroutinetype . ' ' . $curroutine . ';';
                            break;
                    }
                }
                if ($action == 'variables') {
                    // 查看数据库变量信息
                    $sqlstmt = 'select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_variables;';
                } else if ($action == 'create') {
                    // 查看创建数据库命令
                    $sqlstmt = 'show create database ' . $curdbname . ';';
                }
            }
            ?>
            <form action="?action=run">
                <textarea name="sqlstmt" cols="134" rows="9"><?php echo $sqlstmt; ?></textarea><br />
                <input type="hidden" name="db" value="<?php echo $curdbname ?>" />
                <input type="hidden" name="table" value="<?php echo $curtable ?>" />
                <button type="submit">执行</button><button type="reset">重置</button>
            </form>
            <div class="resultgrid">
                <?php
                if ($sqlstmt && strpos($sqlstmt, ' ') > 0) {
                    $sqlstmt_prefix = substr($sqlstmt, 0, strpos($sqlstmt, ' '));
                    switch ($sqlstmt_prefix) {
                        case 'select':
                        case 'show':
                        case 'desc':
                            try {
                                $list = $dbutil->query($sqlstmt);
                                if ($list) {
                                    $i = 0;
                                    echo '<table cellpadding="0" cellspacing="0" class="grid">';
                                    foreach ($list as $row) {
                                        if ($i < 1) {
                                            echo '<tr><th>#</th><th>', implode('</th><th>', array_keys($row)), '</th><tr>';
                                        }
                                        $i++;
                                        echo '<tr><td>', $i, '</td><td>', implode('</td><td>', array_values($row)), '</td></tr>';
                                    }
                                    echo '</table>';
                                } else {
                                    echo '<p>没有查询到结果!</p>';
                                }
                            } catch (Exception $ex) {
                                echo '<p>', $ex->getMessage(), '</p>';
                            }
                            break;
                        case 'insert':
                        case 'update':
                        case 'delete':
                        case 'truncate':
                        case 'alert':
                            try {
                                $result = $dbutil->execute($sqlstmt);
                                echo '<p>执行结果影响' . $result . '行!</p>';
                            } catch (Exception $ex) {
                                echo '<p>', $ex->getMessage(), '</p>';
                            }
                            break;
                        case 'eg.':
                            break;
                        default:
                            echo '<p>未支持命令：', $sqlstmt, '</p>';
                            break;
                    }
                }
                ?>
            </div>
        </main>
        <div style="clear: both"></div>
        <script src="//code.jquery.com/jquery-1.11.3.min.js"></script>
        <script>
            $('legend a').click(function () {
                var 　$a = $(this);
                if ($a.text() == '[-]') {
                    $a.text('[+]').attr('title', '展开');
                    $a.parent().next().hide();
                } else {
                    $a.text('[-]').attr('title', '收起');
                    $a.parent().next().show();
                }
            })
            $('select').change(function () {
                var db = $(this).val();
                if (db.length > 0) {
                    location.href = '?db=' + db;
                }
            })
        </script>
    </body>
</html>
<?php

class DbUtil {

    protected $dbconfig;
    protected $dsn;
    protected $link;

    public function __construct(array $dbcfg) {
        $this->dbconfig = $dbcfg;
        $this->dsn = $this->getDSN($dbcfg);
        if (empty($this->dsn)) {
            throw new Exception('数据库配置文件无效!');
        }
        try {
            $this->link = new PDO($this->dsn, $dbcfg['user'], $dbcfg['password']);
        } catch (Exception $ex) {
            throw $ex;
        }
    }

    public function setDb($dbname) {
        $this->link->exec('use ' . $dbname);
    }

    public function getDSN(array $dbcfg) {
        $dbtype = $dbcfg['type'];
        switch ($dbtype) {
            case 'mysql':
                $dsnarr = ['host' => $dbcfg['host'], 'port' => $dbcfg['port'], 'charset' => $dbcfg['charset']];
                if (!empty($dbcfg['database'])) {
                    $dsnarr['dbname'] = $dbcfg['database'];
                }
                $dsn = '';
                foreach ($dsnarr as $name => $value) {
                    $dsn .= $name . '=' . $value . ';';
                }
                $dsn = 'mysql:' . $dsn;
                break;
            default:
                $dsn = '';
                break;
        }
        return $dsn;
    }

    function execute($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            return $stmt->rowCount();
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    function query($sqlstmt, $params = [], $fetch_style = PDO::FETCH_ASSOC) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            $list = $stmt->fetchAll($fetch_style);
            return $list;
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    function find($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            // 不能用foreach，参考bindParam陷阱：http://www.laruence.com/2012/10/16/2831.html
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        $stmt->execute();
        while ($row = $stmt->fetch()) {
            return $row;
        }
        return null;
    }

    function getDbVariables() {
        $sqlstmt = 'select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_variables;';
        $list = $this->query($sqlstmt);
        return $list;
    }

    /**
     * 返回所有数据库
     * @return array SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
     */
    function getDatabases() {
        $sqlstmt = 'select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.schemata where SCHEMA_NAME not in (\'mysql\',\'information_schema\',\'performance_schema\');';
        $list = $this->query($sqlstmt);
        return $list;
    }

    /**
     * 返回所有数据表
     * @param string $dbname
     * @return array TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT
     */
    function getTables($dbname) {
        $list = $this->query('select TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=:database AND TABLE_TYPE=\'BASE TABLE\';', ['database' => $dbname]);
        return $list;
    }

    /**
     * 返回所有列
     * @param string $dbname
     * @param string $tablename
     * @return array COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
     */
    function getColumns($dbname, $tablename) {
        $list = $this->query('select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA=:database AND TABLE_NAME=:table', ['database' => $dbname, 'table' => $tablename]);
        return $list;
    }

    /**
     * 返回所有视图
     * @param string $dbname
     * @return array TABLE_NAME
     */
    function getViews($dbname) {
        $list = $this->query('select TABLE_NAME from information_schema.views where TABLE_SCHEMA=:database;', ['database' => $dbname]);
        return $list;
    }

    /**
     * 返回所有程序
     * @param string $dbname
     * @return array ROUTINE_NAME, ROUTINE_TYPE
     */
    function getRoutine($dbname) {
        $list = $this->query('select ROUTINE_NAME, ROUTINE_TYPE from information_schema.routines WHERE ROUTINE_SCHEMA=:database order by ROUTINE_TYPE;', ['database' => $dbname]);
        return $list;
    }

}

/**
 * 获取用户输入
 * @param string $name
 * @param mixed $defv
 * @return mixed
 */
function input($name, $defv = '') {
    return isset($_REQUEST[$name]) ? $_REQUEST[$name] : $defv;
}

/**
 * 截短字符串
 * @param string $str
 * @param integer $len
 * @return string
 */
function short_text($str, $len) {
    return strlen($str) > $len ? substr($str, 0, $len) . '...' : $str;
}

/**
 * 获取表单令牌
 * @return string
 */
function get_token() {
    return isset($_SESSION[SESSION_PREFIX . 'token']) ? $_SESSION[SESSION_PREFIX . 'token'] : false;
}

/**
 * 生成表单令牌
 * @return string
 */
function generate_token() {
    $token = date('ymdhis') . rand(100, 999);
    $_SESSION[SESSION_PREFIX . 'token'] = $token;
    return $token;
}

/**
 * 检测用户登录
 */
function checklogin($token, $admins) {
    $posttoken = htmlspecialchars(input('token'));
    if ($posttoken) {
        generate_token(); // 重新生成token
        if ($posttoken != $token) {
            exit('非法来源!');
        }
        $loginid = strval(htmlspecialchars(input('user')));
        $loginpwd = htmlspecialchars(input('pass'));
        if (!$loginid || !$loginpwd) {
            exit('表单填写不完整!');
        }
        if (!array_key_exists($loginid, $admins)) {
            exit('帐号不存在!');
        }
        if ($loginpwd != $admins[$loginid]) {
            exit('密码错误!');
        }
        $_SESSION[SESSION_PREFIX . 'user'] = $loginid;
        header('location:?loginsuccess');
    } else {
        echo '<form method="post" action="?action=login">';
        echo '<input type="text" name="user" placeholder="用户名" required="required" />';
        echo '<input type="password" name="pass" placeholder="密码" required="required" />';
        echo '<input type="hidden" name="token" value="', $token, '" />';
        echo '<button type="submit">登录</button><button type="reset">重置</button>';
        echo '</form>';
    }
}

/**
 * 注销登录
 */
function logout() {
    unset($_SESSION[SESSION_PREFIX . 'user']);
    unset($_SESSION[SESSION_PREFIX . 'token']);
    header('location:?logoutsuccess');
}
